package com.dao;

import java.util.List;

import com.model.Student;
import com.util.JdbcUtil;

public class StudentDao {
	
	JdbcUtil jdbc = new JdbcUtil();
	
	public void update(Student stu) throws Exception{
		String sql="update student set sname = ?,age = ?,sex = ?,phone = ?,school = ?,grade = ?,major = ? where sid = ?";
		jdbc.updatePreparedStatement(sql, stu.getSname(),stu.getAge(),stu.getSex(),stu.getPhone()
				,stu.getSchool(),stu.getGrade(),stu.getMajor(),stu.getSid());
		jdbc.close();
	}

	public List<Student> query(String sid,Integer cp,Integer pagesize) {
		StringBuffer sb = new StringBuffer();
		sb.append("select sid, sname, age, sex, phone, school, grade, major from (");
		sb.append("select rownum as rn,t.* from (");
		
		/******最内层sql******/
		StringBuffer sql = new StringBuffer();
		sql.append("select * from student where 1=1 ");
		if(sid!=null && !sid.equals("")){
			sql.append(" and sid like '%"+sid+"%'");
		}
		sql.append("order by sid asc");
		/******最内层sql******/
		
		sb.append(sql);
		sb.append(") t where rownum<="+(cp*pagesize)+") t2 where t2.rn>"+(cp-1)*pagesize);
		
		List<Student> stus = jdbc.queryPreparedStatement(sb.toString(), Student.class);
		jdbc.close();
		return stus;
	}
	
	//查询总条数
	public int count(String sid){
		StringBuffer sql = new StringBuffer();
		sql.append("select * from student where 1=1 ");
		if(sid!=null && !sid.equals("")){
			sql.append(" and sid like '%"+sid+"%'");
		}
		sql.append("order by sid asc");
		List<Student> temp = jdbc.queryPreparedStatement(sql.toString(), Student.class);
		jdbc.close();
		return temp.size();
	}
	
	public void add(Student stu) throws Exception{
		String sql="insert into student(sid, sname, age, sex, phone, school, grade, major) "
				+ "values (?, ?, ?, ?, ?, ?, ?,?)";
		jdbc.updatePreparedStatement(sql, stu.getSid(),stu.getSname(),stu.getAge(),stu.getSex(),stu.getPhone(),
				stu.getSchool(),stu.getGrade(),stu.getMajor());
	}
}













